package com.ruge.task;

import cn.hutool.cron.CronUtil;
import cn.hutool.cron.task.Task;
import com.ruge.util.QQEmailUtil;
import com.ruge.util.TextUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import javax.annotation.PreDestroy;
import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

@Slf4j
@Component
public class SxFdYhdTask {

    @Resource
    private JdbcTemplate jdbcTemplate;

    /**
     * 初始化定时任务
     */
    @PostConstruct
    public void initCronTasks() {

        CronUtil.schedule("0 */30 * * * ?", (Task) () -> {

            String sql = "SELECT ROWNUM AS 序号, t.FDBH, t.FDMC, t.SP_ID, t.SPCODE, t.BARCODE, t.SPMC, t.SPFL1, t.SPFL2, t.ERP_STATUS, t.MC_STATUS, t.MC_STATUS, t.MAX_LOCAL_TM, t.ERP_TM, CASE WHEN MAX_LOCAL_TM > ERP_TM THEN '数据丢失' ELSE '未同步' END AS 比较结果 FROM (SELECT erp.FDBH, erp.FDMC, erp.SP_ID, erp.SPCODE, erp.BARCODE, erp.SPMC, erp.SPFL1_MC || '(' || erp.SPFL1 || ')' SPFL1, erp.SPFL2_MC || '(' || erp.SPFL2 || ')' SPFL2, erp.MC_STATUS AS ERP_STATUS, mc.STATUS AS MC_STATUS, erp.ERP_TM, (SELECT MAX(ERP_TM) FROM S_SX_SPXX_000003) max_LOCAL_TM FROM BMCP_SX_SPXX_V@CS85_PROD_BFCSUSER erp LEFT JOIN S_SX_SPXX_000003 xx ON erp.fdbh = xx.fdbh AND erp.sp_id = xx.sp_id LEFT JOIN S_SPXX_MC_000003 mc ON erp.fdbh = mc.MCID AND erp.sp_id = mc.sp_id WHERE xx.sp_id IS NULL ORDER BY erp.ERP_TM) t";
            List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
//            if (maps.size() > 0) {
            log.info("未同步的商品:{},sql:{}", maps.size(), sql);
            QQEmailUtil.sendHtmlEmail("1345528755@qq.com", "未同步的商品：" + maps.size(), sql + TextUtil.convertToHtmlTable(maps));
//            }
            //sql = "INSERT INTO S_SX_SPXX_000003_TMP(CGBH, CGMC, FDBH, FDMC, SP_ID, SPCODE, BARCODE, SPMC, HTH, WLDW, WLDWMC, JHDJ_HS, SPFL1, SPFL1_MC, SPFL2, SPFL2_MC, SPFL3, SPFL3_MC, SPFL4, SPFL4_MC, SPGG, UNIT, JXSL, SPCD, BZHL, MC_STATUS, ERP_TM, SYNC_DATE, ERP_STATUS,c_date) SELECT CGBH, CGMC, FDBH, FDMC, SP_ID, SPCODE, BARCODE, SPMC, HTH, WLDW, WLDWMC, JHDJ_HS, SPFL1, SPFL1_MC, SPFL2, SPFL2_MC, SPFL3, SPFL3_MC, SPFL4, SPFL4_MC, SPGG, UNIT, JXSL, SPCD, BZHL, MC_STATUS, ERP_TM, SYNC_DATE, ERP_STATUS,c_date FROM (select xx.CGBH, xx.CGMC, xx.FDBH, xx.FDMC, xx.SP_ID, xx.SPCODE, xx.BARCODE, xx.SPMC, xx.HTH, xx.WLDW, xx.WLDWMC, xx.JHDJ_HS, xx.SPFL1, xx.SPFL1_MC, xx.SPFL2, xx.SPFL2_MC, xx.SPFL3, xx.SPFL3_MC, xx.SPFL4, xx.SPFL4_MC, xx.SPGG, xx.UNIT, xx.JXSL, xx.SPCD, xx.BZHL, xx.MC_STATUS, xx.ERP_TM, xx.SYNC_DATE, MC.STATUS ERP_STATUS, sysdate c_date from S_SX_SPXX_000003 xx inner join s_spxx_mc_000003 mc on xx.fdbh = mc.mcid and xx.sp_id = mc.sp_id) XX WHERE EXISTS (SELECT 1 FROM S_SPXX_MC_000003 MC WHERE XX.FDBH = MC.MCID AND XX.SP_ID = MC.SP_ID AND MC.STATUS NOT IN (0, 2, 3))";
            //jdbcTemplate.execute(sql);
            sql = "DELETE FROM S_SX_SPXX_000003 XX WHERE EXISTS (SELECT 1 FROM S_SPXX_MC_000003 MC WHERE XX.FDBH = MC.MCID AND XX.SP_ID = MC.SP_ID AND MC.STATUS NOT IN (0, 2, 3))";
            int update = jdbcTemplate.update(sql);
            // QQEmailUtil.sendTextEmail("1345528755@qq.com", "删除成功条数：" + update, sql);
            // log.info("删除成功条数：" + update, sql);
        });

        CronUtil.schedule("0 */30 * * * ?", (Task) () -> {
            String sql = "select MC.STATUS, yi.jlbh, YI.BZHL, Y.C_USR_ID, Y.C_USR_NAME, Y.C_DATE, Y.FDBH, Y.FDMC, Y.SPFLMC, Y.SPFLBM, YI.SPMC, YI.SP_ID, YI.SPCODE, YI.YHJS, YI.YHSL from S_SX_FD_YHD_000003 Y INNER JOIN S_SX_FD_YHD_ITEM_000003 YI ON Y.JLBH = YI.JLBH INNER JOIN s_spxx_mc_000003 MC ON YI.SP_ID = MC.SP_ID AND Y.FDBH = 999 || MC.MCID WHERE Y.HZYHD IS NULL and MC.STATUS not in (0,2,3)";
            List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
            // QQEmailUtil.sendHtmlEmail("1345528755@qq.com", "要货单的过期商品：" + maps.size(), sql + TextUtil.convertToHtmlTable(maps));
            // log.info("要货单的过期商品：" + maps.size(), sql + TextUtil.convertToHtmlTable(maps));
        });
        // 启动定时任务调度器
        // true表示使用默认的全局调度器
        CronUtil.start(true);
    }

    /**
     * 销毁定时任务
     */
    @PreDestroy
    public void destroyCronTasks() {
        // 停止定时任务调度器
        CronUtil.stop();
    }


}
